-- 创建todos表
CREATE TABLE todos (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  text TEXT NOT NULL CHECK (length(text) > 0),
  completed BOOLEAN DEFAULT FALSE NOT NULL,
  image_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

-- 创建索引以提高查询性能
CREATE INDEX idx_todos_user_id ON todos(user_id);
CREATE INDEX idx_todos_created_at ON todos(created_at DESC);
CREATE INDEX idx_todos_completed ON todos(completed);

-- 创建更新时间戳的触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 创建触发器，自动更新updated_at字段
CREATE TRIGGER update_todos_updated_at 
    BEFORE UPDATE ON todos 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

-- 启用行级安全策略
ALTER TABLE todos ENABLE ROW LEVEL SECURITY;

-- 策略1：用户只能查看自己的todos
CREATE POLICY "用户只能查看自己的todos" ON todos
    FOR SELECT USING (auth.uid() = user_id);

-- 策略2：用户只能插入自己的todos
CREATE POLICY "用户只能创建自己的todos" ON todos
    FOR INSERT WITH CHECK (auth.uid() = user_id);

-- 策略3：用户只能更新自己的todos
CREATE POLICY "用户只能更新自己的todos" ON todos
    FOR UPDATE USING (auth.uid() = user_id);

-- 策略4：用户只能删除自己的todos
CREATE POLICY "用户只能删除自己的todos" ON todos
    FOR DELETE USING (auth.uid() = user_id);

-- 创建一个视图，包含用户的todo统计信息
-- 注意：视图会自动继承基础表的RLS策略，所以用户只能看到自己的统计信息
CREATE VIEW user_todo_stats AS
SELECT 
    user_id,
    COUNT(*) as total_todos,
    COUNT(*) FILTER (WHERE completed = true) as completed_todos,
    COUNT(*) FILTER (WHERE completed = false) as pending_todos,
    ROUND(
        (COUNT(*) FILTER (WHERE completed = true)::DECIMAL / COUNT(*)) * 100, 
        2
    ) as completion_percentage
FROM todos
GROUP BY user_id;

-- 创建一个函数来批量更新todos状态
CREATE OR REPLACE FUNCTION toggle_all_todos(complete_all BOOLEAN)
RETURNS INTEGER AS $$
DECLARE
    updated_count INTEGER;
BEGIN
    UPDATE todos 
    SET completed = complete_all, updated_at = NOW()
    WHERE user_id = auth.uid();
    
    GET DIAGNOSTICS updated_count = ROW_COUNT;
    RETURN updated_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 创建一个函数来清理已完成的todos
CREATE OR REPLACE FUNCTION clear_completed_todos()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM todos 
    WHERE user_id = auth.uid() AND completed = true;
    
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 创建一个安全函数来获取用户的todo统计信息
CREATE OR REPLACE FUNCTION get_user_todo_stats()
RETURNS TABLE(
    total_todos BIGINT,
    completed_todos BIGINT,
    pending_todos BIGINT,
    completion_percentage NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        COUNT(*) as total_todos,
        COUNT(*) FILTER (WHERE completed = true) as completed_todos,
        COUNT(*) FILTER (WHERE completed = false) as pending_todos,
        ROUND(
            (COUNT(*) FILTER (WHERE completed = true)::DECIMAL / NULLIF(COUNT(*), 0)) * 100, 
            2
        ) as completion_percentage
    FROM todos
    WHERE user_id = auth.uid();
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;


